<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
        <title id="ay20941">ALTER TABLE</title>
        <body>
                <p id="sql_command_desc">Changes the definition of a table.</p>
                <section id="section2">
                        <title>Synopsis</title>
                        <codeblock id="sql_command_synopsis">ALTER TABLE [IF EXISTS] [ONLY] <varname>name</varname> 
    <varname>action</varname> [, ... ]

ALTER TABLE [IF EXISTS] [ONLY] <varname>name</varname> 
    RENAME [COLUMN] <varname>column_name</varname> TO <varname>new_column_name</varname>

ALTER TABLE [ IF EXISTS ] [ ONLY ] <varname>name</varname> 
    RENAME CONSTRAINT <varname>constraint_name</varname> TO <varname>new_constraint_name</varname>

ALTER TABLE [IF EXISTS] <varname>name</varname> 
    RENAME TO <varname>new_name</varname>

ALTER TABLE [IF EXISTS] <varname>name</varname> 
    SET SCHEMA <varname>new_schema</varname>

ALTER TABLE ALL IN TABLESPACE <varname>name</varname> [ OWNED BY <varname>role_name</varname> [, ... ] ]
    SET TABLESPACE <varname>new_tablespace</varname> [ NOWAIT ]

ALTER TABLE [IF EXISTS] [ONLY] <varname>name</varname> SET 
     WITH (REORGANIZE=true|false)
   | DISTRIBUTED BY ({<varname>column_name</varname> [<varname>opclass</varname>]} [, ... ] )
   | DISTRIBUTED RANDOMLY
   | DISTRIBUTED REPLICATED 

ALTER TABLE <varname>name</varname>
   [ ALTER PARTITION { <varname>partition_name</varname> | FOR (RANK(<varname>number</varname>)) 
   | FOR (<varname>value</varname>) } [...] ] <varname>partition_action</varname>

where <varname>action</varname> is one of:
                        
  ADD [COLUMN] <varname>column_name data_type</varname> [ DEFAULT <varname>default_expr</varname> ]
      [<varname>column_constraint</varname> [ ... ]]
      [ COLLATE <varname>collation</varname> ]
      [ ENCODING ( <varname>storage_parameter</varname> [,...] ) ]
  DROP [COLUMN] [IF EXISTS] <varname>column_name</varname> [RESTRICT | CASCADE]
  ALTER [COLUMN] <varname>column_name</varname> [ SET DATA ] TYPE <varname>type</varname> [COLLATE <varname>collation</varname>] [USING <varname>expression</varname>]
  ALTER [COLUMN] <varname>column_name</varname> SET DEFAULT <varname>expression</varname>
  ALTER [COLUMN] <varname>column_name</varname> DROP DEFAULT
  ALTER [COLUMN] <varname>column_name</varname> { SET | DROP } NOT NULL
  ALTER [COLUMN] <varname>column_name</varname> SET STATISTICS <varname>integer</varname>
  ALTER [COLUMN] column SET ( <varname>attribute_option</varname> = <varname>value</varname> [, ... ] )
  ALTER [COLUMN] column RESET ( <varname>attribute_option</varname> [, ... ] )
  ADD <varname>table_constraint</varname> [NOT VALID]
  ADD <varname>table_constraint_using_index</varname>
  VALIDATE CONSTRAINT <varname>constraint_name</varname>
  DROP CONSTRAINT [IF EXISTS] <varname>constraint_name</varname> [RESTRICT | CASCADE]
  DISABLE TRIGGER [<varname>trigger_name</varname> | ALL | USER]
  ENABLE TRIGGER [<varname>trigger_name</varname> | ALL | USER]
  CLUSTER ON <varname>index_name</varname>
  SET WITHOUT CLUSTER
  SET WITHOUT OIDS
  SET (<varname>storage_parameter</varname> = <varname>value</varname>)
  RESET (<varname>storage_parameter</varname> [, ... ])
  INHERIT <varname>parent_table</varname>
  NO INHERIT <varname>parent_table</varname>
  OF <codeph>type_name</codeph>
  NOT OF
  OWNER TO <varname>new_owner</varname>
  SET TABLESPACE <varname>new_tablespace</varname></codeblock>
                        <p>where <varname>table_constraint_using_index</varname> is:</p>
                        <pre>  [ CONSTRAINT <varname>constraint_name</varname> ]
  { UNIQUE | PRIMARY KEY } USING INDEX <varname>index_name</varname>
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]</pre>
                        <p>where <varname>partition_action</varname> is one of:</p>
                        <codeblock>  ALTER DEFAULT PARTITION
  DROP DEFAULT PARTITION [IF EXISTS]
  DROP PARTITION [IF EXISTS] { <varname>partition_name</varname> | 
      FOR (RANK(<varname>number</varname>)) | FOR (<varname>value</varname>) } [CASCADE]
  TRUNCATE DEFAULT PARTITION
  TRUNCATE PARTITION { <varname>partition_name</varname> | FOR (RANK(<varname>number</varname>)) | 
      FOR (<varname>value</varname>) }
  RENAME DEFAULT PARTITION TO <varname>new_partition_name</varname>
  RENAME PARTITION { <varname>partition_name</varname> | FOR (RANK(<varname>number</varname>)) | 
      FOR (<varname>value</varname>) } TO <varname>new_partition_name</varname>
  ADD DEFAULT PARTITION <varname>name</varname> [ ( <varname>subpartition_spec</varname> ) ]
  ADD PARTITION [<varname>partition_name</varname>] <varname>partition_element</varname>
     [ ( <varname>subpartition_spec</varname> ) ]
  EXCHANGE PARTITION { <varname>partition_name</varname> | FOR (RANK(<varname>number</varname>)) | 
       FOR (<varname>value</varname>) } WITH TABLE <varname>table_name</varname>
        [ WITH | WITHOUT VALIDATION ]
  EXCHANGE DEFAULT PARTITION WITH TABLE <varname>table_name</varname>
   [ WITH | WITHOUT VALIDATION ]
  SET SUBPARTITION TEMPLATE (<varname>subpartition_spec</varname>)
  SPLIT DEFAULT PARTITION
    {  AT (<varname>list_value</varname>)
     | START([<varname>datatype</varname>] <varname>range_value</varname>) [INCLUSIVE | EXCLUSIVE] 
        END([<varname>datatype</varname>] <varname>range_value</varname>) [INCLUSIVE | EXCLUSIVE] }
    [ INTO ( PARTITION <varname>new_partition_name</varname>, 
             PARTITION <varname>default_partition_name</varname> ) ]
  SPLIT PARTITION { <varname>partition_name</varname> | FOR (RANK(<varname>number</varname>)) | 
     FOR (<varname>value</varname>) } AT (<varname>value</varname>) 
    [ INTO (PARTITION <varname>partition_name</varname>, PARTITION <varname>partition_name</varname>)]  </codeblock>
                        <p>where <varname>partition_element</varname> is:</p>
                        <codeblock>    VALUES (<varname>list_value</varname> [,...] )
  | START ([<varname>datatype</varname>] '<varname>start_value</varname>') [INCLUSIVE | EXCLUSIVE]
     [ END ([<varname>datatype</varname>] '<varname>end_value</varname>') [INCLUSIVE | EXCLUSIVE] ]
  | END ([<varname>datatype</varname>] '<varname>end_value</varname>') [INCLUSIVE | EXCLUSIVE]
[ WITH ( <varname>partition_storage_parameter</varname>=<varname>value</varname> [, ... ] ) ]
[ TABLESPACE <varname>tablespace</varname> ]</codeblock>
                        <p>where <varname>subpartition_spec</varname> is:</p>
                        <codeblock><varname>subpartition_element</varname> [, ...]</codeblock>
                        <p>and <varname>subpartition_element</varname> is:</p>
                        <codeblock>   DEFAULT SUBPARTITION <varname>subpartition_name</varname>
  | [SUBPARTITION <varname>subpartition_name</varname>] VALUES (<varname>list_value</varname> [,...] )
  | [SUBPARTITION <varname>subpartition_name</varname>] 
     START ([<varname>datatype</varname>] '<varname>start_value</varname>') [INCLUSIVE | EXCLUSIVE]
     [ END ([<varname>datatype</varname>] '<varname>end_value</varname>') [INCLUSIVE | EXCLUSIVE] ]
     [ EVERY ( [<varname>number | datatype</varname>] '<varname>interval_value</varname>') ]
  | [SUBPARTITION <varname>subpartition_name</varname>] 
     END ([<varname>datatype</varname>] '<varname>end_value</varname>') [INCLUSIVE | EXCLUSIVE]
     [ EVERY ( [<varname>number | datatype</varname>] '<varname>interval_value</varname>') ]
[ WITH ( <varname>partition_storage_parameter</varname>=<varname>value</varname> [, ... ] ) ]
[ TABLESPACE <varname>tablespace</varname> ]</codeblock>
                        <p>where <varname>storage_parameter</varname> is:</p>
                        <codeblock>   appendoptimized={TRUE|FALSE}
   blocksize={8192-2097152}
   orientation={COLUMN|ROW}
   compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE}
   compresslevel={0-9}
   fillfactor={10-100}
   [oids=FALSE]</codeblock>
                </section>
                <section id="section3">
                        <title>Description</title>
                        <p><codeph>ALTER TABLE</codeph> changes the definition of an existing table.
                                There are several subforms: </p>
                        <ul>
                                <li id="ay136723"><b>ADD COLUMN</b> — Adds a new column to the
                                        table, using the same syntax as <codeph><xref
                                                  href="CREATE_TABLE.xml">CREATE
                                                TABLE</xref></codeph>. The <codeph>ENCODING</codeph>
                                        clause is valid only for append-optimized, column-oriented
                                                tables.<p>When you add a column to an
                                                append-optimized, column-oriented table, Greenplum
                                                Database sets each data compression parameter for
                                                the column (<codeph>compresstype</codeph>,
                                                  <codeph>compresslevel</codeph>, and
                                                  <codeph>blocksize</codeph>) based on the following
                                                setting, in order of preference.<ol
                                                  id="ol_hqk_l4w_kmb">
                                                  <li>The compression parameter setting specified in
                                                  the <codeph>ALTER TABLE</codeph> command
                                                  <codeph>ENCODING</codeph> clause.</li>
                                                  <li>If the server configuration parameter
                                                  <codeph><xref
                                                  href="../config_params/guc-list.xml#gp_add_column_inherits_table_setting"
                                                  >gp_add_column_inherits_table_setting</xref></codeph>
                                                  is <codeph>on</codeph>, use the table's data
                                                  compression parameters specified in the
                                                  <codeph>WITH</codeph> clause when the table was
                                                  created. The default server configuration
                                                  parameter default is <codeph>off</codeph>, the
                                                  <codeph>WITH</codeph> clause parameters are
                                                  ignored.</li>
                                                  <li>The compression parameter setting specified in
                                                  the server configuration parameter <codeph><xref
                                                  href="../config_params/guc-list.xml#gp_default_storage_options"
                                                  >gp_default_storage_option</xref></codeph>.</li>
                                                  <li>The default compression parameter value.</li>
                                                </ol></p><p>For append-optimized and hash tables,
                                                  <codeph>ADD COLUMN</codeph> requires a table
                                                rewrite. For information about table rewrites
                                                performed by <codeph>ALTER TABLE</codeph>, see <xref
                                                  href="#topic1/section5" format="dita"
                                                >Notes</xref>.</p></li>
                                <li><b>DROP COLUMN [IF EXISTS]</b> — Drops a column from a table.
                                        Note that if you drop table columns that are being used as
                                        the Greenplum Database distribution key, the distribution
                                        policy for the table will be changed to <codeph>DISTRIBUTED
                                                RANDOMLY</codeph>. Indexes and table constraints
                                        involving the column are automatically dropped as well. You
                                        need to say <codeph>CASCADE</codeph> if anything outside the
                                        table depends on the column (such as views). If <codeph>IF
                                                EXISTS</codeph> is specified and the column does not
                                        exist, no error is thrown; a notice is issued instead.</li>
                                <li><b>IF EXISTS</b> — Do not throw an error if the table does not
                                        exist. A notice is issued in this case. </li>
                                <li id="ay136879"><b>SET DATA TYPE</b> — This form changes the data
                                        type of a column of a table. Note that you cannot alter
                                        column data types that are being used as distribution or
                                        partitioning keys. Indexes and simple table constraints
                                        involving the column will be automatically converted to use
                                        the new column type by reparsing the originally supplied
                                        expression. The optional <codeph>COLLATE</codeph> clause
                                        specifies a collation for the new column; if omitted, the
                                        collation is the default for the new column type. The
                                        optional <codeph>USING</codeph> clause specifies how to
                                        compute the new column value from the old. If omitted, the
                                        default conversion is the same as an assignment cast from
                                        old data type to new. A <codeph>USING</codeph> clause must
                                        be provided if there is no implicit or assignment cast from
                                        old to new type.
                                        <note>GPORCA supports collation only when all columns in the
                                                query use the same collation. If columns in the
                                                query use different collations, then Greenplum uses
                                                the Postgres Planner.</note><p>Changing a column
                                                data type requires a table rewrite. For information
                                                about table rewrites performed by <codeph>ALTER
                                                  TABLE</codeph>, see <xref href="#topic1/section5"
                                                  format="dita">Notes</xref>.</p></li>
                                <li id="ay136904"><b>SET/DROP DEFAULT</b> — Sets or removes the
                                        default value for a column. Default values only apply in
                                        subsequent <codeph>INSERT</codeph> or
                                                <codeph>UPDATE</codeph> commands; they do not cause
                                        rows already in the table to change. </li>
                                <li id="ay136929"><b>SET/DROP NOT NULL</b> — Changes whether a
                                        column is marked to allow null values or to reject null
                                        values. You can only use <codeph>SET NOT NULL</codeph> when
                                        the column contains no null values.</li>
                                <li id="ay136944"><b>SET STATISTICS</b> — Sets the per-column
                                        statistics-gathering target for subsequent
                                                <codeph>ANALYZE</codeph> operations. The target can
                                        be set in the range 0 to 10000, or set to -1 to revert to
                                        using the system default statistics target
                                                (<codeph>default_statistics_target</codeph>). When
                                        set to 0, no statistics are collected.</li>
                                <li>
                                        <p><b>SET ( <varname>attribute_option</varname> =
                                                  <varname>value</varname> [, ... ]) </b></p>
                                        <p><b>RESET ( <varname>attribute_option [, ...]
                                                  )</varname></b>— Sets or resets per-attribute
                                                options. Currently, the only defined per-attribute
                                                options are <codeph>n_distinct</codeph> and
                                                  <codeph>n_distinct_inherited</codeph>, which
                                                override the number-of-distinct-values estimates
                                                made by subsequent <codeph><xref
                                                  href="ANALYZE.xml#topic1">ANALYZE</xref></codeph>
                                                operations. <codeph>n_distinct</codeph> affects the
                                                statistics for the table itself, while
                                                  <codeph>n_distinct_inherited</codeph> affects the
                                                statistics gathered for the table plus its
                                                inheritance children. When set to a positive value,
                                                  <codeph>ANALYZE</codeph> will assume that the
                                                column contains exactly the specified number of
                                                distinct non-null values. When set to a negative
                                                value, which must be greater than or equal to -1,
                                                  <codeph>ANALYZE</codeph> will assume that the
                                                number of distinct non-null values in the column is
                                                linear in the size of the table; the exact count is
                                                to be computed by multiplying the estimated table
                                                size by the absolute value of the given number. For
                                                example, a value of -1 implies that all values in
                                                the column are distinct, while a value of -0.5
                                                implies that each value appears twice on the
                                                average. This can be useful when the size of the
                                                table changes over time, since the multiplication by
                                                the number of rows in the table is not performed
                                                until query planning time. Specify a value of 0 to
                                                revert to estimating the number of distinct values
                                                normally.</p>
                                </li>
                                <li id="ay137022"><b>ADD <varname>table_constraint</varname> [NOT
                                                VALID]</b> — Adds a new constraint to a table (not
                                        just a partition) using the same syntax as <codeph>CREATE
                                                TABLE</codeph>. The <codeph>NOT VALID</codeph>
                                        option is currently only allowed for foreign key and
                                                <codeph>CHECK</codeph> constraints. If the
                                        constraint is marked <codeph>NOT VALID</codeph>, Greenplum
                                        Database skips the potentially-lengthy initial check to
                                        verify that all rows in the table satisfy the constraint.
                                        The constraint will still be enforced against subsequent
                                        inserts or updates (that is, they'll fail unless there is a
                                        matching row in the referenced table, in the case of foreign
                                        keys; and they'll fail unless the new row matches the
                                        specified check constraints). But the database will not
                                        assume that the constraint holds for all rows in the table,
                                        until it is validated by using the <codeph>VALIDATE
                                                CONSTRAINT</codeph> option. Constraint checks are
                                        skipped at create table time, so the <codeph><xref
                                                href="CREATE_TABLE.xml#topic1"/></codeph> syntax
                                        does not include this option. </li>
                                <li><b>VALIDATE CONSTRAINT</b> — This form validates a foreign key
                                        constraint that was previously created as <codeph>NOT
                                                VALID</codeph>, by scanning the table to ensure
                                        there are no rows for which the constraint is not satisfied.
                                        Nothing happens if the constraint is already marked valid.
                                        The advantage of separating validation from initial creation
                                        of the constraint is that validation requires a lesser lock
                                        on the table than constraint creation does.</li>
                                <li><b>ADD <varname>table_constraint_using_index</varname></b> —
                                        Adds a new <codeph>PRIMARY KEY</codeph> or
                                                <codeph>UNIQUE</codeph> constraint to a table based
                                        on an existing unique index. All the columns of the index
                                        will be included in the constraint. The index cannot have
                                        expression columns nor be a partial index. Also, it must be
                                        a b-tree index with default sort ordering. These
                                        restrictions ensure that the index is equivalent to one that
                                        would be built by a regular <codeph>ADD PRIMARY KEY</codeph>
                                        or <codeph>ADD UNIQUE</codeph> command. <p>Adding a
                                                  <codeph>PRIMARY KEY</codeph> or
                                                  <codeph>UNIQUE</codeph> constraint to a table
                                                based on an existing unique index is not supported
                                                on a partitioned table.</p><p>If <codeph>PRIMARY
                                                  KEY</codeph> is specified, and the index's columns
                                                are not already marked <codeph>NOT NULL</codeph>,
                                                then this command will attempt to do <codeph>ALTER
                                                  COLUMN SET NOT NULL</codeph> against each such
                                                column. That requires a full table scan to verify
                                                the column(s) contain no nulls. In all other cases,
                                                this is a fast operation. </p><p>If a constraint
                                                name is provided then the index will be renamed to
                                                match the constraint name. Otherwise the constraint
                                                will be named the same as the index.</p><p>After
                                                this command is run, the index is "owned" by
                                                the constraint, in the same way as if the index had
                                                been built by a regular <codeph>ADD PRIMARY
                                                  KEY</codeph> or <codeph>ADD UNIQUE</codeph>
                                                command. In particular, dropping the constraint will
                                                make the index disappear too.</p></li>
                                <li id="ay137038"><b>DROP CONSTRAINT [IF EXISTS]</b> — Drops the
                                        specified constraint on a table. If <codeph>IF
                                                EXISTS</codeph> is specified and the constraint does
                                        not exist, no error is thrown. In this case a notice is
                                        issued instead.</li>
                                <li id="ay137055"><b>DISABLE/ENABLE TRIGGER</b> — Disables or
                                        enables trigger(s) belonging to the table. A disabled
                                        trigger is still known to the system, but is not run 
                                        when its triggering event occurs. For a deferred trigger,
                                        the enable status is checked when the event occurs, not when
                                        the trigger function is actually run. One may disable
                                        or enable a single trigger specified by name, or all
                                        triggers on the table, or only user-created triggers.
                                        Disabling or enabling constraint triggers requires superuser
                                                privileges.<note> triggers are not supported in
                                                Greenplum Database. Triggers in general have very
                                                limited functionality due to the parallelism of
                                                Greenplum Database.</note></li>
                                <li id="ay137071"><b>CLUSTER ON/SET WITHOUT CLUSTER</b> — Selects or
                                        removes the default index for future
                                                <codeph>CLUSTER</codeph> operations. It does not
                                        actually re-cluster the table. Note that
                                                <codeph>CLUSTER</codeph> is not the recommended way
                                        to physically reorder a table in Greenplum Database because
                                        it takes so long. It is better to recreate the table with
                                                  <codeph><xref href="./CREATE_TABLE_AS.xml#topic1"
                                                  type="topic" format="dita"/></codeph> and order it
                                        by the index column(s). <note><codeph>CLUSTER ON</codeph> is
                                                not supported on append-optimized
                                        tables.</note></li>
                                <li id="ay137152"><b>SET WITHOUT OIDS</b> — Removes the OID system
                                        column from the table.<note type="warning"><ph
                                                  otherprops="pivotal">VMware does not support
                                                  using <codeph>SET WITH OIDS</codeph> or
                                                  <codeph>oids=TRUE</codeph> to assign an OID system
                                                  column. </ph>On large tables, such as those in a
                                                typical Greenplum Database system, using OIDs for
                                                table rows can cause wrap-around of the 32-bit OID
                                                counter. Once the counter wraps around, OIDs can no
                                                longer be assumed to be unique, which not only makes
                                                them useless to user applications, but can also
                                                cause problems in the Greenplum Database system
                                                catalog tables. In addition, excluding OIDs from a
                                                table reduces the space required to store the table
                                                on disk by 4 bytes per row, slightly improving
                                                performance. You cannot create OIDS on a partitioned
                                                or column-oriented table (an error is displayed).
                                                This syntax is deprecated and will be removed in a
                                                future Greenplum release.</note></li>
                                <li id="ay137192"><b>SET ( FILLFACTOR = <varname>value</varname>) /
                                                RESET (FILLFACTOR)</b> — Changes the fillfactor for
                                        the table. The fillfactor for a table is a percentage
                                        between 10 and 100. 100 (complete packing) is the default.
                                        When a smaller fillfactor is specified,
                                                <codeph>INSERT</codeph> operations pack table pages
                                        only to the indicated percentage; the remaining space on
                                        each page is reserved for updating rows on that page. This
                                        gives <codeph>UPDATE</codeph> a chance to place the updated
                                        copy of a row on the same page as the original, which is
                                        more efficient than placing it on a different page. For a
                                        table whose entries are never updated, complete packing is
                                        the best choice, but in heavily updated tables smaller
                                        fillfactors are appropriate. Note that the table contents
                                        will not be modified immediately by this command. You will
                                        need to rewrite the table to get the desired effects. That
                                        can be done with <codeph><xref href="VACUUM.xml#topic1"
                                                /></codeph> or one of the forms of <codeph>ALTER
                                                TABLE</codeph> that forces a table rewrite. For
                                        information about the forms of <codeph>ALTER TABLE</codeph>
                                        that perform a table rewrite, see <xref
                                                href="#topic1/section5" format="dita"
                                        >Notes</xref>.</li>
                                <li id="ay141947"><b>SET DISTRIBUTED</b> — Changes the distribution
                                        policy of a table. Changing a hash distribution policy, or
                                        changing to or from a replicated policy, will cause the
                                        table data to be physically redistributed on disk, which can
                                        be resource intensive.</li>
                                <li id="ay137232"><b>INHERIT <varname>parent_table</varname> / NO
                                                INHERIT <varname>parent_table</varname></b> — Adds
                                        or removes the target table as a child of the specified
                                        parent table. Queries against the parent will include
                                        records of its child table. To be added as a child, the
                                        target table must already contain all the same columns as
                                        the parent (it could have additional columns, too). The
                                        columns must have matching data types, and if they have
                                                <codeph>NOT NULL</codeph> constraints in the parent
                                        then they must also have <codeph>NOT NULL</codeph>
                                        constraints in the child. There must also be matching
                                        child-table constraints for all <codeph>CHECK</codeph>
                                        constraints of the parent, except those marked
                                        non-inheritable (that is, created with <codeph>ALTER TABLE
                                                ... ADD CONSTRAINT ... NO INHERIT</codeph>) in the parent, which are ignored; all
                                        child-table constraints matched must not be marked
                                        non-inheritable. Currently <codeph>UNIQUE</codeph>,
                                                <codeph>PRIMARY KEY</codeph>, and <codeph>FOREIGN
                                                KEY</codeph> constraints are not considered, but
                                        this may change in the future.</li>
                                <li>OF <varname>type_name</varname> — This form links the table to a
                                        composite type as though <codeph>CREATE TABLE OF</codeph>
                                        had formed it. The table's list of column names and types
                                        must precisely match that of the composite type; the
                                        presence of an <codeph>oid</codeph> system column is
                                        permitted to differ. The table must not inherit from any
                                        other table. These restrictions ensure that <codeph>CREATE
                                                TABLE OF</codeph> would permit an equivalent table
                                        definition. </li>
                                <li><b>NOT OF</b> — This form dissociates a typed table from its
                                        type. </li>
                                <li id="ay137262"><b>OWNER</b> — Changes the owner of the table,
                                        sequence, or view to the specified user. </li>
                                <li id="ay137271"><b>SET TABLESPACE</b> — Changes the table's
                                        tablespace to the specified tablespace and moves the data
                                        file(s) associated with the table to the new tablespace.
                                        Indexes on the table, if any, are not moved; but they can be
                                        moved separately with additional <codeph>SET
                                                TABLESPACE</codeph> commands. All tables in the
                                        current database in a tablespace can be moved by using the
                                                <codeph>ALL IN TABLESPACE</codeph> form, which will
                                        lock all tables to be moved first and then move each one.
                                        This form also supports <codeph>OWNED BY</codeph>, which
                                        will only move tables owned by the roles specified. If the
                                                <codeph>NOWAIT</codeph> option is specified then the
                                        command will fail if it is unable to acquire all of the
                                        locks required immediately. Note that system catalogs are
                                        not moved by this command, use <codeph>ALTER
                                                DATABASE</codeph> or explicit <codeph>ALTER
                                                TABLE</codeph> invocations instead if desired. The
                                                <codeph>information_schema</codeph> relations are
                                        not considered part of the system catalogs and will be
                                        moved. See also <codeph>CREATE TABLESPACE</codeph>. If
                                        changing the tablespace of a partitioned table, all child
                                        table partitions will also be moved to the new tablespace. </li>
                                <li id="ay137292"><b>RENAME</b> — Changes the name of a table (or an
                                        index, sequence, view, or materialized view), the name of an
                                        individual column in a table, or the name of a constraint of
                                        the table. There is no effect on the stored data. Note that
                                        Greenplum Database distribution key columns cannot be
                                        renamed.</li>
                                <li id="ay137307"><b>SET SCHEMA</b> — Moves the table into another
                                        schema. Associated indexes, constraints, and sequences owned
                                        by table columns are moved as well. </li>
                                <li id="ay139526"><b>ALTER PARTITION | DROP PARTITION | RENAME
                                                PARTITION | TRUNCATE PARTITION | ADD PARTITION |
                                                SPLIT PARTITION | EXCHANGE PARTITION | SET
                                                SUBPARTITION TEMPLATE </b>— Changes the structure of
                                        a partitioned table. In most cases, you must go through the
                                        parent table to alter one of its child table
                                        partitions.</li>
                        </ul>
                        <note>If you add a partition to a table that has subpartition encodings, the
                                new partition inherits the storage directives for the subpartitions.
                                For more information about the precedence of compression settings,
                                see <xref href="../../admin_guide/ddl/ddl-storage.html#topic40" format="html" scope="external">Using Compression</xref>.</note>
                        <p>All the forms of <codeph>ALTER TABLE</codeph> that act on a single table,
                                except <codeph>RENAME</codeph> and <codeph>SET SCHEMA</codeph>, can
                                be combined into a list of multiple alterations to apply together.
                                For example, it is possible to add several columns and/or alter the
                                type of several columns in a single command. This is particularly
                                useful with large tables, since only one pass over the table need be
                                made. </p>
                        <p>You must own the table to use <codeph>ALTER TABLE</codeph>. To change the
                                schema or tablespace of a table, you must also have
                                        <codeph>CREATE</codeph> privilege on the new schema or
                                tablespace. To add the table as a new child of a parent table, you
                                must own the parent table as well. To alter the owner, you must also
                                be a direct or indirect member of the new owning role, and that role
                                must have <codeph>CREATE</codeph> privilege on the table's schema.
                                To add a column or alter a column type or use the
                                        <codeph>OF</codeph> clause, you must also have
                                        <codeph>USAGE</codeph> privilege on the data type. A
                                superuser has these privileges automatically.</p>
                        <note>Memory usage increases significantly when a table has many partitions,
                                if a table has compression, or if the blocksize for a table is
                                large. If the number of relations associated with the table is
                                large, this condition can force an operation on the table to use
                                more memory. For example, if the table is a CO table and has a large
                                number of columns, each column is a relation. An operation like
                                        <codeph>ALTER TABLE ALTER COLUMN</codeph> opens all the
                                columns in the table allocates associated buffers. If a CO table has
                                40 columns and 100 partitions, and the columns are compressed and
                                the blocksize is 2 MB (with a system factor of 3), the system
                                attempts to allocate 24 GB, that is (40 ×100) × (2 ×3) MB or 24
                                GB.</note>
                </section>
                <section id="section4">
                        <title>Parameters</title>
                        <parml>
                                <plentry>
                                        <pt>ONLY</pt>
                                        <pd>Only perform the operation on the table name specified.
                                                If the <codeph>ONLY</codeph> keyword is not used,
                                                the operation will be performed on the named table
                                                and any child table partitions associated with that
                                                table. <note>Adding or dropping a column, or
                                                  changing a column's type, in a parent or
                                                  descendant table only is not permitted. The parent
                                                  table and its descendents must always have the
                                                  same columns and types.</note></pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>name</varname>
                                        </pt>
                                        <pd>The name (possibly schema-qualified) of an existing
                                                table to alter. If <codeph>ONLY</codeph> is
                                                specified, only that table is altered. If
                                                  <codeph>ONLY</codeph> is not specified, the table
                                                and all its descendant tables (if any) are updated.
                                                <note type="note">Constraints can only be added to
                                                  an entire table, not to a partition. Because of
                                                  that restriction, the <varname>name</varname>
                                                  parameter can only contain a table name, not a
                                                  partition name. </note></pd>
                                </plentry>
                        </parml>
                        <parml>
                                <plentry>
                                        <pt>
                                                <varname>column_name</varname>
                                        </pt>
                                        <pd>Name of a new or existing column. Note that Greenplum
                                                Database distribution key columns must be treated
                                                with special care. Altering or dropping these
                                                columns can change the distribution policy for the
                                                table.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>new_column_name</varname>
                                        </pt>
                                        <pd>New name for an existing column.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>new_name</varname>
                                        </pt>
                                        <pd>New name for the table. </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>type</varname>
                                        </pt>
                                        <pd>Data type of the new column, or new data type for an
                                                existing column. If changing the data type of a
                                                Greenplum distribution key column, you are only
                                                allowed to change it to a compatible type (for
                                                example, <codeph>text</codeph> to
                                                  <codeph>varchar</codeph> is OK, but
                                                  <codeph>text</codeph> to <codeph>int</codeph> is
                                                not).</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>table_constraint</varname>
                                        </pt>
                                        <pd>New table constraint for the table. Note that foreign
                                                key constraints are currently not supported in
                                                Greenplum Database. Also a table is only allowed one
                                                unique constraint and the uniqueness must be within
                                                the Greenplum Database distribution key.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>constraint_name</varname>
                                        </pt>
                                        <pd>Name of an existing constraint to drop.</pd>
                                </plentry>
                                <plentry>
                                        <pt>CASCADE</pt>
                                        <pd>Automatically drop objects that depend on the dropped
                                                column or constraint (for example, views referencing
                                                the column). </pd>
                                </plentry>
                                <plentry>
                                        <pt>RESTRICT</pt>
                                        <pd>Refuse to drop the column or constraint if there are any
                                                dependent objects. This is the default behavior.
                                        </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>trigger_name</varname>
                                        </pt>
                                        <pd>Name of a single trigger to disable or enable. Note that
                                                Greenplum Database does not support triggers.</pd>
                                </plentry>
                                <plentry>
                                        <pt>ALL</pt>
                                        <pd>Disable or enable all triggers belonging to the table
                                           including constraint related triggers. This requires superuser privilege 
                                           if any of the triggers are internally generated constraint triggers such 
                                           as those that are used to implement foreign key constraints or deferrable 
                                           uniqueness and exclusion constraints.</pd>
                                </plentry>
                                <plentry>
                                        <pt>USER</pt>
                                        <pd>Disable or enable all triggers belonging to the table except for 
                                            internally generated constraint triggers such as those that are used
                                            to implement foreign key constraints or deferrable uniqueness and
                                            exclusion constraints.
                                        </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>index_name</varname>
                                        </pt>
                                        <pd>The index name on which the table should be marked for
                                                clustering. Note that <codeph>CLUSTER</codeph> is
                                                not the recommended way to physically reorder a
                                                table in Greenplum Database because it takes so
                                                long. It is better to recreate the table with
                                                  <codeph><xref href="./CREATE_TABLE_AS.xml#topic1"
                                                  type="topic" format="dita"/></codeph> and order it
                                                by the index column(s).</pd>
                                </plentry>
                                <plentry>
                                        <pt>FILLFACTOR</pt>
                                        <pd>Set the fillfactor percentage for a table.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>value</varname>
                                        </pt>
                                        <pd>The new value for the <codeph>FILLFACTOR</codeph>
                                                parameter, which is a percentage between 10 and 100.
                                                100 is the default.</pd>
                                </plentry>
                                <plentry>
                                        <pt>DISTRIBUTED BY ({<varname>column_name</varname>
                                                  [<varname>opclass</varname>]}) | DISTRIBUTED
                                                RANDOMLY | DISTRIBUTED REPLICATED</pt>
                                        <pd>Specifies the distribution policy for a table. Changing
                                                a hash distribution policy causes the table data to
                                                be physically redistributed, which can be resource
                                                intensive. If you declare the same hash distribution
                                                policy or change from hash to random distribution,
                                                data will not be redistributed unless you declare
                                                  <codeph>SET WITH (REORGANIZE=true)</codeph>.</pd>
                                        <pd>Changing to or from a replicated distribution policy
                                                causes the table data to be redistributed.</pd>
                                </plentry>
                                <plentry>
                                        <pt>REORGANIZE=true|false</pt>
                                        <pd>Use <codeph>REORGANIZE=true</codeph> when the hash
                                                distribution policy has not changed or when you have
                                                changed from a hash to a random distribution, and
                                                you want to redistribute the data anyways.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>parent_table</varname>
                                        </pt>
                                        <pd>A parent table to associate or de-associate with this
                                                table. </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>new_owner</varname>
                                        </pt>
                                        <pd>The role name of the new owner of the table. </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>new_tablespace</varname>
                                        </pt>
                                        <pd>The name of the tablespace to which the table will be
                                                moved. </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>new_schema</varname>
                                        </pt>
                                        <pd>The name of the schema to which the table will be moved.
                                        </pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>parent_table_name</varname>
                                        </pt>
                                        <pd>When altering a partitioned table, the name of the
                                                top-level parent table.</pd>
                                </plentry>
                                <plentry>
                                        <pt>ALTER [DEFAULT] PARTITION</pt>
                                        <pd>If altering a partition deeper than the first level of
                                                partitions, use <codeph>ALTER PARTITION</codeph>
                                                clauses to specify which subpartition in the
                                                hierarchy you want to alter. For each partition
                                                level in the table hierarchy that is above the
                                                target partition, specify the partition that is
                                                related to the target partition in an <codeph>ALTER
                                                  PARTITION</codeph> clause.</pd>
                                </plentry>
                                <plentry>
                                        <pt>DROP [DEFAULT] PARTITION</pt>
                                        <pd>Drops the specified partition. If the partition has
                                                subpartitions, the subpartitions are automatically
                                                dropped as well.</pd>
                                </plentry>
                                <plentry>
                                        <pt>TRUNCATE [DEFAULT] PARTITION</pt>
                                        <pd>Truncates the specified partition. If the partition has
                                                subpartitions, the subpartitions are automatically
                                                truncated as well.</pd>
                                </plentry>
                                <plentry>
                                        <pt>RENAME [DEFAULT] PARTITION</pt>
                                        <pd>Changes the partition name of a partition (not the
                                                relation name). Partitioned tables are created using
                                                the naming convention:
                                                  <codeph>&lt;</codeph><varname>parentname</varname><codeph>&gt;_&lt;</codeph><varname>level</varname><codeph>&gt;_prt_&lt;</codeph><varname>partition_name</varname><codeph>&gt;</codeph>.</pd>
                                </plentry>
                                <plentry>
                                        <pt>ADD DEFAULT PARTITION</pt>
                                        <pd>Adds a default partition to an existing partition
                                                design. When data does not match to an existing
                                                partition, it is inserted into the default
                                                partition. Partition designs that do not have a
                                                default partition will reject incoming rows that do
                                                not match to an existing partition. Default
                                                partitions must be given a name.</pd>
                                </plentry>
                                <plentry>
                                        <pt>ADD PARTITION</pt>
                                        <pd><varname>partition_element</varname> - Using the
                                                existing partition type of the table (range or
                                                list), defines the boundaries of new partition you
                                                are adding.</pd>
                                        <pd><varname>name</varname> - A name for this new
                                                partition.</pd>
                                        <pd><b>VALUES</b> - For list partitions, defines the
                                                value(s) that the partition will contain.</pd>
                                        <pd><b>START</b> - For range partitions, defines the
                                                starting range value for the partition. By default,
                                                start values are <codeph>INCLUSIVE</codeph>. For
                                                example, if you declared a start date of
                                                  '<codeph>2016-01-01</codeph>', then the partition
                                                would contain all dates greater than or equal to
                                                  '<codeph>2016-01-01</codeph>'. Typically the data
                                                type of the <codeph>START</codeph> expression is the
                                                same type as the partition key column. If that is
                                                not the case, then you must explicitly cast to the
                                                intended data type.</pd>
                                        <pd><b>END</b> - For range partitions, defines the ending
                                                range value for the partition. By default, end
                                                values are <codeph>EXCLUSIVE</codeph>. For example,
                                                if you declared an end date of
                                                  '<codeph>2016-02-01</codeph>', then the partition
                                                would contain all dates less than but not equal to
                                                  '<codeph>2016-02-01</codeph>'. Typically the data
                                                type of the <codeph>END</codeph> expression is the
                                                same type as the partition key column. If that is
                                                not the case, then you must explicitly cast to the
                                                intended data type.</pd>
                                        <pd><b>WITH</b> - Sets the table storage options for a
                                                partition. For example, you may want older
                                                partitions to be append-optimized tables and newer
                                                partitions to be regular heap tables. See
                                                  <codeph><xref href="./CREATE_TABLE.xml#topic1"
                                                  type="topic" format="dita"/></codeph> for a
                                                description of the storage options.</pd>
                                        <pd><b>TABLESPACE</b> - The name of the tablespace in which
                                                the partition is to be created.</pd>
                                        <pd><varname>subpartition_spec</varname> - Only allowed on
                                                partition designs that were created without a
                                                subpartition template. Declares a subpartition
                                                specification for the new partition you are adding.
                                                If the partitioned table was originally defined
                                                using a subpartition template, then the template
                                                will be used to generate the subpartitions
                                                automatically.</pd>
                                </plentry>
                                <plentry>
                                        <pt>EXCHANGE [DEFAULT] PARTITION</pt>
                                        <pd>Exchanges another table into the partition hierarchy
                                                into the place of an existing partition. In a
                                                multi-level partition design, you can only exchange
                                                the lowest level partitions (those that contain
                                                data). </pd>
                                        <pd>The Greenplum Database server configuration parameter
                                                  <codeph>gp_enable_exchange_default_partition</codeph>
                                                controls availability of the <codeph>EXCHANGE
                                                  DEFAULT PARTITION</codeph> clause. The default
                                                value for the parameter is <codeph>off</codeph>. The
                                                clause is not available and Greenplum Database
                                                returns an error if the clause is specified in an
                                                  <codeph>ALTER TABLE</codeph> command.</pd>
                                        <pd>For information about the parameter, see <xref
                                                  href="../config_params/guc_config.xml#topic1"
                                                  />.<note type="warning">Before you exchange the
                                                  default partition, you must ensure the data in the
                                                  table to be exchanged, the new default partition,
                                                  is valid for the default partition. For example,
                                                  the data in the new default partition must not
                                                  contain data that would be valid in other leaf
                                                  child partitions of the partitioned table.
                                                  Otherwise, queries against the partitioned table
                                                  with the exchanged default partition that are
                                                  run by GPORCA might return incorrect
                                                  results.</note></pd>
                                        <pd><b>WITH TABLE</b>
                                                <varname>table_name</varname> - The name of the
                                                table you are swapping into the partition design.
                                                You can exchange a table where the table data is
                                                stored in the database. For example, the table is
                                                created with the <codeph>CREATE TABLE</codeph>
                                                command. The table must have the same number of
                                                columns, column order, column names, column types,
                                                and distribution policy as the parent table.</pd>
                                        <pd>With the <codeph>EXCHANGE PARTITION</codeph> clause, you
                                                can also exchange a readable external table (created
                                                with the <codeph>CREATE EXTERNAL TABLE</codeph>
                                                command) into the partition hierarchy in the place
                                                of an existing leaf child partition. If you specify
                                                a readable external table, you must also specify the
                                                  <codeph>WITHOUT VALIDATION</codeph> clause to skip
                                                table validation against the <codeph>CHECK</codeph>
                                                constraint of the partition you are exchanging.</pd>
                                        <pd>Exchanging a leaf child partition with an external table
                                                is not supported if the partitioned table contains a
                                                column with a check constraint or a <codeph>NOT
                                                  NULL</codeph> constraint.</pd>
                                        <pd>You cannot exchange a partition with a replicated table.
                                                Exchanging a partition with a partitioned table or a child partition of a partitioned
                                                table is not supported.</pd>
                                        <pd><b>WITH</b> | <b>WITHOUT VALIDATION</b> - Validates that
                                                the data in the table matches the
                                                  <codeph>CHECK</codeph> constraint of the partition
                                                you are exchanging. The default is to validate the
                                                data against the <codeph>CHECK</codeph> constraint.
                                                <note type="warning">If you specify the
                                                  <codeph>WITHOUT VALIDATION</codeph> clause, you
                                                  must ensure that the data in table that you are
                                                  exchanging for an existing child leaf partition is
                                                  valid against the <codeph>CHECK</codeph>
                                                  constraints on the partition. Otherwise, queries
                                                  against the partitioned table might return
                                                  incorrect results.</note></pd>
                                </plentry>
                                <plentry>
                                        <pt>SET SUBPARTITION TEMPLATE</pt>
                                        <pd>Modifies the subpartition template for an existing
                                                partition. After a new subpartition template is set,
                                                all new partitions added will have the new
                                                subpartition design (existing partitions are not
                                                modified).</pd>
                                </plentry>
                                <plentry>
                                        <pt>SPLIT DEFAULT PARTITION</pt>
                                        <pd>Splits a default partition. In a multi-level partition,
                                                only a range partition can be split, not a list
                                                partition, and you can only split the lowest level
                                                default partitions (those that contain data).
                                                Splitting a default partition creates a new
                                                partition containing the values specified and leaves
                                                the default partition containing any values that do
                                                not match to an existing partition.</pd>
                                        <pd><b>AT</b> - For list partitioned tables, specifies a
                                                single list value that should be used as the
                                                criteria for the split. </pd>
                                        <pd><b>START</b> - For range partitioned tables, specifies a
                                                starting value for the new partition. </pd>
                                        <pd><b>END</b> - For range partitioned tables, specifies an
                                                ending value for the new partition.</pd>
                                        <pd><b>INTO</b> - Allows you to specify a name for the new
                                                partition. When using the <codeph>INTO</codeph>
                                                clause to split a default partition, the second
                                                partition name specified should always be that of
                                                the existing default partition. If you do not know
                                                the name of the default partition, you can look it
                                                up using the <varname>pg_partitions</varname>
                                                view.</pd>
                                </plentry>
                                <plentry>
                                        <pt>SPLIT PARTITION</pt>
                                        <pd>Splits an existing partition into two partitions. In a
                                                multi-level partition, only a range partition can be
                                                split, not a list partition, and you can only split
                                                the lowest level partitions (those that contain
                                                data). </pd>
                                        <pd><b>AT</b> - Specifies a single value that should be used
                                                as the criteria for the split. The partition will be
                                                divided into two new partitions with the split value
                                                specified being the starting range for the
                                                  <varname>latter</varname> partition.</pd>
                                        <pd><b>INTO</b> - Allows you to specify names for the two
                                                new partitions created by the split.</pd>
                                </plentry>
                                <plentry>
                                        <pt>
                                                <varname>partition_name</varname>
                                        </pt>
                                        <pd>The given name of a partition. The given partition name
                                                is the <codeph>partitionname</codeph> column value
                                                in the <i><xref
                                                  href="../../ref_guide/system_catalogs/pg_partitions.xml"
                                                  >pg_partitions</xref></i> system view.</pd>
                                </plentry>
                                <plentry>
                                        <pt>FOR (RANK(number))</pt>
                                        <pd>For range partitions, the rank of the partition in the
                                                range. </pd>
                                </plentry>
                                <plentry>
                                        <pt>FOR ('<varname>value</varname>')</pt>
                                        <pd>Specifies a partition by declaring a value that falls
                                                within the partition boundary specification. If the
                                                value declared with <codeph>FOR</codeph> matches to
                                                both a partition and one of its subpartitions (for
                                                example, if the value is a date and the table is
                                                partitioned by month and then by day), then
                                                  <codeph>FOR</codeph> will operate on the first
                                                level where a match is found (for example, the
                                                monthly partition). If your intent is to operate on
                                                a subpartition, you must declare so as follows:
                                                  <codeph>ALTER TABLE <varname>name</varname> ALTER
                                                  PARTITION FOR ('2016-10-01') DROP PARTITION FOR
                                                  ('2016-10-01');</codeph></pd>
                                </plentry>
                        </parml>
                </section>
                <section id="section5">
                        <title>Notes</title>
                        <p>The table name specified in the <codeph>ALTER TABLE</codeph> command
                                cannot be the name of a partition within a table. </p>
                        <p>Take special care when altering or dropping columns that are part of the
                                Greenplum Database distribution key as this can change the
                                distribution policy for the table.</p>
                        <p>Greenplum Database does not currently support foreign key constraints.
                                For a unique constraint to be enforced in Greenplum Database, the
                                table must be hash-distributed (not <codeph>DISTRIBUTED
                                        RANDOMLY</codeph>), and all of the distribution key columns
                                must be the same as the initial columns of the unique constraint
                                columns.</p>
                        <p>Adding a <codeph>CHECK</codeph> or <codeph>NOT NULL</codeph> constraint
                                requires scanning the table to verify that existing rows meet the
                                constraint, but does not require a table rewrite.</p>
                        <p>This table lists the <codeph>ALTER TABLE</codeph> operations that require
                                a table rewrite when performed on tables defined with the specified
                                type of table storage.</p>
                        <table frame="all" rowsep="1" colsep="1" id="table_r2g_24r_qmb">
                                <title>ALTER TABLE Operations that Require Table Rewrite</title>
                                <tgroup cols="4">
                                        <colspec colname="c1" colnum="1" colwidth="1.0*"/>
                                        <colspec colname="c2" colnum="2" colwidth="1.0*"/>
                                        <colspec colname="c3" colnum="3" colwidth="1.0*"/>
                                        <colspec colname="c4" colnum="4" colwidth="1.0*"/>
                                        <thead>
                                                <row>
                                                  <entry>Operation (See Note)</entry>
                                                  <entry>Append-Optimized, Column-Oriented</entry>
                                                  <entry>Append-Optimized</entry>
                                                  <entry>Heap</entry>
                                                </row>
                                        </thead>
                                        <tbody>
                                                <row>
                                                  <entry><codeph>ALTER COLUMN TYPE</codeph></entry>
                                                  <entry>Yes</entry>
                                                  <entry>Yes</entry>
                                                  <entry>Yes</entry>
                                                </row>
                                                <row>
                                                  <entry><codeph>ADD COLUMN</codeph></entry>
                                                  <entry>No</entry>
                                                  <entry>Yes</entry>
                                                  <entry>Yes</entry>
                                                </row>
                                        </tbody>
                                </tgroup>
                        </table>
                        <note>Dropping a system <codeph>oid</codeph> column also requires a table
                                rewrite. <p>When a column is added with <codeph>ADD COLUMN</codeph>,
                                        all existing rows in the table are initialized with the
                                        column's default value, or <codeph>NULL</codeph> if no
                                                <codeph>DEFAULT</codeph> clause is specified. Adding
                                        a column with a non-null default or changing the type of an
                                        existing column will require the entire table and indexes to
                                        be rewritten. As an exception, if the <codeph>USING</codeph>
                                        clause does not change the column contents and the old type
                                        is either binary coercible to the new type or an
                                        unconstrained domain over the new type, a table rewrite is
                                        not needed, but any indexes on the affected columns must
                                        still be rebuilt. Table and/or index rebuilds may take a
                                        significant amount of time for a large table; and will
                                        temporarily require as much as double the disk space.
                                </p></note>
                        <note type="important">The forms of <codeph>ALTER TABLE</codeph> that
                                perform a table rewrite on an append-optimized table are not
                                MVCC-safe. After a table rewrite, the table will appear empty to
                                concurrent transactions if they are using a snapshot taken before
                                the rewrite occurred. See <xref
                                        href="https://www.postgresql.org/docs/9.4/mvcc-caveats.html"
                                        scope="external" format="html">MVCC Caveats</xref> for more
                                details.</note>
                        <p>You can specify multiple changes in a single <codeph>ALTER TABLE</codeph>
                                command, which will be done in a single pass over the table. </p>
                        <p>The <codeph>DROP COLUMN</codeph> form does not physically remove the
                                column, but simply makes it invisible to SQL operations. Subsequent
                                insert and update operations in the table will store a null value
                                for the column. Thus, dropping a column is quick but it will not
                                immediately reduce the on-disk size of your table, as the space
                                occupied by the dropped column is not reclaimed. The space will be
                                reclaimed over time as existing rows are updated. If you drop the
                                system <codeph>oid</codeph> column, however, the table is rewritten
                                immediately.</p>
                        <p>To force immediate reclamation of space occupied by a dropped column, you
                                can run one of the forms of <codeph>ALTER TABLE</codeph> that
                                performs a rewrite of the whole table. This results in
                                reconstructing each row with the dropped column replaced by a null
                                value. </p>
                        <p>The <codeph>USING</codeph> option of <codeph>SET DATA TYPE</codeph> can
                                actually specify any expression involving the old values of the row;
                                that is, it can refer to other columns as well as the one being
                                converted. This allows very general conversions to be done with the
                                        <codeph>SET DATA TYPE</codeph> syntax. Because of this
                                flexibility, the <codeph>USING</codeph> expression is not applied to
                                the column's default value (if any); the result might not be a
                                constant expression as required for a default. This means that when
                                there is no implicit or assignment cast from old to new type,
                                        <codeph>SET DATA TYPE</codeph> might fail to convert the
                                default even though a <codeph>USING</codeph> clause is supplied. In
                                such cases, drop the default with <codeph>DROP DEFAULT</codeph>,
                                perform the <codeph>ALTER TYPE</codeph>, and then use <codeph>SET
                                        DEFAULT</codeph> to add a suitable new default. Similar
                                considerations apply to indexes and constraints involving the
                                column. </p>
                        <p>If a table is partitioned or has any descendant tables, it is not
                                permitted to add, rename, or change the type of a column, or rename
                                an inherited constraint in the parent table without doing the same
                                to the descendants. This ensures that the descendants always have
                                columns matching the parent. </p>
                        <p>To see the structure of a partitioned table, you can use the view
                                                <codeph><xref
                                                href="../system_catalogs/pg_partitions.xml"
                                                type="topic" format="dita"/></codeph>. This view can
                                help identify the particular partitions you may want to alter.</p>
                        <p>A recursive <codeph>DROP COLUMN</codeph> operation will remove a
                                descendant table's column only if the descendant does not inherit
                                that column from any other parents and never had an independent
                                definition of the column. A nonrecursive <codeph>DROP
                                        COLUMN</codeph> (<codeph>ALTER TABLE ONLY ... DROP
                                        COLUMN</codeph>) never removes any descendant columns, but
                                instead marks them as independently defined rather than inherited. </p>
                        <p>The <codeph>TRIGGER</codeph>, <codeph>CLUSTER</codeph>,
                                        <codeph>OWNER</codeph>, and <codeph>TABLESPACE</codeph>
                                actions never recurse to descendant tables; that is, they always act
                                as though <codeph>ONLY</codeph> were specified. Adding a constraint
                                recurses only for <codeph>CHECK</codeph> constraints that are not
                                marked <codeph>NO INHERIT</codeph>.</p>
                        <p>These <codeph>ALTER PARTITION</codeph> operations are supported if no
                                data is changed on a partitioned table that contains a leaf child
                                partition that has been exchanged to use an external table.
                                Otherwise, an error is returned.<ul id="ul_hcw_mrn_qs">
                                        <li>Adding or dropping a column.</li>
                                        <li>Changing the data type of column.</li>
                                </ul></p>
                        <p>These <codeph>ALTER PARTITION</codeph> operations are not supported for a
                                partitioned table that contains a leaf child partition that has been
                                exchanged to use an external table:<ul id="ul_edw_mrn_qs">
                                        <li>Setting a subpartition template.</li>
                                        <li>Altering the partition properties.</li>
                                        <li>Creating a default partition.</li>
                                        <li>Setting a distribution policy.</li>
                                        <li>Setting or dropping a <codeph>NOT NULL</codeph>
                                                constraint of column.</li>
                                        <li>Adding or dropping constraints.</li>
                                        <li>Splitting an external partition.</li>
                                </ul></p>
                        <p>Changing any part of a system catalog table is not permitted.</p>
                </section>
                <section id="section6">
                        <title>Examples</title>
                        <p>Add a column to a table:</p>
                        <codeblock>ALTER TABLE distributors ADD COLUMN address varchar(30);</codeblock>
                        <p>Rename an existing column:</p>
                        <codeblock>ALTER TABLE distributors RENAME COLUMN address TO city;</codeblock>
                        <p>Rename an existing table:</p>
                        <codeblock>ALTER TABLE distributors RENAME TO suppliers;</codeblock>
                        <p>Add a not-null constraint to a column:</p>
                        <codeblock>ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;</codeblock>
                        <p>Rename an existing constraint:</p>
                        <codeblock>ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;</codeblock>
                        <p>Add a check constraint to a table and all of its children:</p>
                        <codeblock>ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK 
(char_length(zipcode) = 5);</codeblock>
                        <p>To add a check constraint only to a table and not to its children:</p>
                        <codeblock>ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;</codeblock>
                        <p>(The check constraint will not be inherited by future children, either.)</p>

                        <p>Remove a check constraint from a table and all of its children:</p>
                        <codeblock>ALTER TABLE distributors DROP CONSTRAINT zipchk;</codeblock>
                        <p>Remove a check constraint from one table only:</p>
                        <codeblock>ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;</codeblock>
                        <p>(The check constraint remains in place for any child tables that inherit
                                        <codeph>distributors</codeph>.)</p>
                        <p>Move a table to a different schema:</p>
                        <codeblock>ALTER TABLE myschema.distributors SET SCHEMA yourschema;</codeblock>
                        <p>Change the distribution policy of a table to replicated:</p>
                        <codeblock>ALTER TABLE myschema.distributors SET DISTRIBUTED REPLICATED;</codeblock>
                        <p>Add a new partition to a partitioned table:</p>
                        <codeblock>ALTER TABLE sales ADD PARTITION 
            START (date '2017-02-01') INCLUSIVE 
            END (date '2017-03-01') EXCLUSIVE;</codeblock>
                        <p>Add a default partition to an existing partition design:</p>
                        <codeblock>ALTER TABLE sales ADD DEFAULT PARTITION other;</codeblock>
                        <p>Rename a partition:</p>
                        <codeblock>ALTER TABLE sales RENAME PARTITION FOR ('2016-01-01') TO 
jan08;</codeblock>
                        <p>Drop the first (oldest) partition in a range sequence:</p>
                        <codeblock>ALTER TABLE sales DROP PARTITION FOR (RANK(1));</codeblock>
                        <p>Exchange a table into your partition design:</p>
                        <codeblock>ALTER TABLE sales EXCHANGE PARTITION FOR ('2016-01-01') WITH 
TABLE jan08;</codeblock>
                        <p>Split the default partition (where the existing default partition's name
                                is <codeph>other</codeph>) to add a new monthly partition for
                                January 2017:</p>
                        <codeblock>ALTER TABLE sales SPLIT DEFAULT PARTITION 
START ('2017-01-01') INCLUSIVE 
END ('2017-02-01') EXCLUSIVE 
INTO (PARTITION jan09, PARTITION other);</codeblock>
                        <p>Split a monthly partition into two with the first partition containing
                                dates January 1-15 and the second partition containing dates January
                                16-31:</p>
                        <codeblock>ALTER TABLE sales SPLIT PARTITION FOR ('2016-01-01')
AT ('2016-01-16')
INTO (PARTITION jan081to15, PARTITION jan0816to31);</codeblock>
                        <p>For a multi-level partitioned table that consists of three levels, year,
                                quarter, and region, exchange a leaf partition
                                        <codeph>region</codeph> with the table
                                        <codeph>region_new</codeph>. </p>
                        <codeblock>ALTER TABLE sales ALTER PARTITION year_1 ALTER PARTITION quarter_4 EXCHANGE PARTITION region WITH TABLE region_new ;</codeblock>
                        <p>In the previous command, the two <codeph>ALTER PARTITION</codeph> clauses
                                identify which <codeph>region</codeph> partition to exchange. Both
                                clauses are required to identify the specific partition to
                                exchange.</p>
                </section>
                <section id="section7">
                        <title>Compatibility</title>
                        <p>The forms <codeph>ADD</codeph> (without <codeph>USING INDEX</codeph>),
                                        <codeph>DROP</codeph>, <codeph>SET DEFAULT</codeph>, and
                                        <codeph>SET DATA TYPE</codeph> (without
                                        <codeph>USING</codeph>) conform with the SQL standard. The
                                other forms are Greenplum Database extensions of the SQL standard.
                                Also, the ability to specify more than one manipulation in a single
                                        <codeph>ALTER TABLE</codeph> command is an extension.</p>
                        <p><codeph>ALTER TABLE DROP COLUMN</codeph> can be used to drop the only
                                column of a table, leaving a zero-column table. This is an extension
                                of SQL, which disallows zero-column tables.</p>
                </section>
                <section id="section8">
                        <title>See Also</title>
                        <p><codeph><xref href="./CREATE_TABLE.xml#topic1" type="topic" format="dita"
                                        /></codeph>, <codeph><xref href="./DROP_TABLE.xml#topic1"
                                                type="topic" format="dita"/></codeph></p>
                </section>
        </body>
</topic>
